package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.报价单表Model;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.util.List;

/**
 * Created by sxy on 2016/7/11.
 */
@Transactional
public interface 报价单表Repository extends JpaRepository<报价单表Model, Integer>, JpaSpecificationExecutor<报价单表Model> {

    @Modifying
    @Query("update 报价单表Model m set m.金融A = ?2, m.金融B = ?3, m.金融C = ?4 where m.报价编号 = ?1")
    int set金融AAnd金融BAnd金融CBy报价编号(int id, Integer ida, Integer idb, Integer idc);

    @Modifying
    @Query("update 报价单表Model m set m.金融战败原因编号 = ?2 where m.报价编号 = ?1")
    int set金融战败原因编号By报价编号(int id, Integer reasonId);

    @Modifying
    @Query("update 报价单表Model m set m.新保战败原因编号 = ?2 where m.保险编号 = ?1")
    int set新保战败原因编号By保险编号(int id, Integer reasonId);

    @Query("select m from 报价单表Model m where m.顾客编号 = ?1 order by m.更新时间 desc")
    报价单表Model findBy顾客编号(int customerId);

    @Query("select m from 报价单表Model m where m.报价编号 = ?1 ")
    报价单表Model findBy报价编号(int 报价编号);

    @Query("select a from 报价单表Model a where a.初始报价单号=?1  ORDER BY a.报价单号 DESC")
    List<报价单表Model> findBy初始报价编号(String initQuoteNumber);

    @Modifying
    @Query("update 报价单表Model m set m.当前状态 = ?2 where m.报价编号 = ?1")
    int set当前状态By报价编号(int id, String status);

    @Query("select a from 报价单表Model a where a.初始报价单号 = ?1 order by a.报价编号 desc")
    List<报价单表Model> getListByInitQuoteNumber(String initQuoteNumber);

    @Query("select a from 报价单表Model a where a.初始报价单号 = ?1  and a.是否还原点='是' order by a.报价编号 desc ")
    List<报价单表Model> getListByInitQuoteNumbers(String initQuoteNumber);

    @Modifying
    @Query("update 报价单表Model a set a.库存编号=?2 where a.报价编号=?1")
    int updateModelById(int id, int storeId);
    
    @Modifying
    @Query("UPDATE 报价单表Model t2 SET t2.库存编号=?1 WHERE t2.初始报价单号=?3 AND t2.报价编号>?2")
    int updateModelByQuoteNumber(int storeId, int quoteNumber, String initQuoteNumber);

    @Query("select a from 报价单表Model a where a.初始报价单号=?1 and a.是否还原点='是'")
    报价单表Model getQuoteListByInitQuoteNumber(String initQuoteNumber);

    @Modifying
    @Query("UPDATE 报价单表Model t2 SET t2.驳回时间=?2,t2.是否驳回='是',t2.invalidReason='2'  WHERE t2.报价编号 >?1 and t2.初始报价单号=?3 ")
    int update是否驳回ById(int id, Timestamp ts, String initQuoteNumber);

    @Query("select a from 报价单表Model a where a.初始报价单号=?1 and a.是否还原点='是' order by a.报价编号 desc")
    List<报价单表Model> getListByInitQuoteNumberAnd是否还原点(String initQuoteNumber);

    @Modifying
    @Query("update 报价单表Model a set a.还原点创建时间=?3, a.是否还原点=?2 where a.报价编号=?1")
    int set还原点AndTime(Integer id, String status, Timestamp ts);

    @Modifying
    @Query("update 报价单表Model a set a.还原点创建时间=?3, a.是否还原点=?2,a.库存编号=?4 where a.报价编号=?1")
    int set还原点And库存编号AndTime(Integer id, String status, Timestamp ts, int storeId);

    @Query("select m from 报价单表Model m where m.顾客编号 = ?1 order by m.更新时间 desc ")
    List<报价单表Model> findBy顾客编号LimitOne(int customerId);


    @Query("select m from 报价单表Model m where m.顾客编号 = ?1  and m.销售顾问=?2 ")
    List<报价单表Model> findByCustomerLimitOne(int customerId, String scman);


    @Query("select a from 报价单表Model a where a.初始报价单号=?1 and a.是否还原点='是' and a.是否驳回='否'  order by a.报价编号 desc")
    List<报价单表Model> getListByInitQuoteAnd(String initQuoteNumber);

    @Query("select m from 报价单表Model m where  m.初始报价单号 =?1 AND m.是否还原点='是' AND m.是否驳回 ='否'  order by m.报价编号 desc ")
    List<报价单表Model> findModelByRestore(String initQuoteNumber);

    @Query("select b.库存编号 from 报价单表Model b where b.初始报价单号= (select a.初始报价单号 from 报价单表Model a WHERE a.报价编号 = ?1 ) and b.是否还原点='是' and b.是否驳回 ='否' ORDER BY b.报价编号 desc")
    List<Integer> getContractStockNumber (int 报价编号);

    @Query("select a from 报价单表Model a where a.库存编号=?1  order by a.报价编号 desc")
    List<报价单表Model> getListByStockId(Integer stockId);

    @Modifying
    @Query("update 报价单表Model a set a.销售顾问=?1 where a.初始报价单号=?2 ")
    int updateBjdOnXsgw(String xsgw,   String csbjdh);


    @Query("select b.报价编号 from 报价单表Model b where b.初始报价单号= ?1 ")
    List<Integer> getQuotationListByInitQuotation(String initQuotation);

    @Query(value = "select * from 报价单表 where 是否还原点 = '是' and 初始报价单号 = ?1 order by 报价编号 desc limit 1,1 ",nativeQuery = true)
    报价单表Model getLastSecondQuotationOrder(String quotationOrderNum);

    @Query(value = "select a.* from 报价单表 a where a.初始报价单号 = ?1 order by a.报价编号 desc limit 1",nativeQuery = true )
    报价单表Model findTheLastForFinance(String initQuoteNumber);

    @Query("select b from 报价单表Model b where b.初始报价单号= ?1 ")
    List<报价单表Model> getQuotationListByInitNo(String initQuotation);
}
